IF OBJECT_ID('dbo.RptFinancialDeliverableMasterReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptFinancialDeliverableMasterReport
    IF OBJECT_ID('dbo.RptFinancialDeliverableMasterReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptFinancialDeliverableMasterReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptFinancialDeliverableMasterReport >>>'
END
go

-- =======================================================================
-- Procedure Name: dbo.RptFinancialDeliverableMasterReport
--
-- Author:      Ponraja Chandran
--
-- Create date: 10/09/2009
--
-- Description: Gets Raw data of Financial Deliverables Summary
--
-- Serves as a drill down to the Report, RptFinancialDeliverableMasterDetail
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
--exec RptFinancialDeliverableMasterReport '01/01/2009','06/01/2009',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ORG',11055  
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/09/09    PC        Created based on RptFinancialDeliverableReport.sql
--
-- =======================================================================
 
 
                  
CREATE PROCEDURE dbo.RptFinancialDeliverableMasterReport    
     (@StartDate                        DATETIME,      
     @EndDate                           DATETIME,         
     @DeliverableStatus                 VARCHAR(5000) = NULL,    
     @Institution                       VARCHAR(100) = NULL,    
     @Department                        VARCHAR(100) = NULL,    
     @Unit                              VARCHAR(100) = NULL,    
     @FundNumber                        VARCHAR(1000) = NULL,    
     @PrincipalInvestigator             VARCHAR(100) = NULL,     
     @OriginatingSponsor                VARCHAR(100) = NULL,    
     @Sponsor                           VARCHAR(100) = NULL,         
     @SubcontractSpecialist             VARCHAR(100) = NULL,    
     @GroupBy                           VARCHAR(20) = NULL,   
     --@SelectedHeaderStatus              VARCHAR(1000)= NULL,     
     @DomainUserId                      INT     )     
AS    
BEGIN    
     -- Initial Settings    
     SET NOCOUNT ON        
    
     -- Declare Local Variables    
     DECLARE   @Error              INT,    
               @RowCount           INT,    
               @ReturnCode         INT     
                            
 declare @ReportDate DATETIME               
 set @ReportDate = convert(datetime, convert(varchar(11),getdate()))                   
         
     DECLARE   @Proposals TABLE (    
                    FolderNumber             VARCHAR(12)    NULL,    
                    InfoEdPropNumber         VARCHAR(50)    NULL,    
                    Institution              VARCHAR(50)    NULL,    
                    Department               VARCHAR(50)    NULL,    
                    Unit                     VARCHAR(50)    NULL,    
                    ChiefCode                VARCHAR(4)     NULL,    
                    SponsorName              VARCHAR(75)    NULL,    
                    SponsorTypeName          VARCHAR(50)    NULL,    
                    SecondarySponsor         VARCHAR(75)    NULL,    
                    ProposalShortTitle       VARCHAR(64)    NULL,    
                    PrincipalInvestigator    VARCHAR(64)    NULL,    
                    ProjectStartDate         DATETIME       NULL,    
                    ProjectEndDate           DATETIME       NULL,    
                    InstrumentType           VARCHAR(50)    NULL,    
                    SubContractorName        VARCHAR(75)    NULL,    
                    BudgetProjectStartDate   DATETIME       NULL,    
                    BudgetProjectEndDate     DATETIME       NULL,    
                    ProjectAwardedAmount     MONEY          NULL,    
                    BudgetStartDate          DATETIME       NULL,    
                    BudgetEndDate            DATETIME       NULL,    
                    BudgetFundNumber         VARCHAR(20)    NULL,    
                    ExpenseAmount            MONEY          NULL,    
                    IndirectExpenses         MONEY          NULL,    
                    DirectExpenses           MONEY          NULL,    
                    CashBalance              MONEY          NULL,    
                    ReportId                 BIGINT         NULL,    
                    ReportName               VARCHAR(50)    NULL,    
                    REPORTCAT                VARCHAR(80)    NULL,    
                    REPORTSTATID             INT      NULL,    
                    REPORTSTAT               VARCHAR(80)    NULL,    
                    SubmittedDate            DATETIME       NULL,    
                    --DueDate                  DATETIME       NULL,    
                    StatusDate               DATETIME       NULL,    
                    CompletedDate            DATETIME       NULL,    
                    DaysFromDueToCompleted   VARCHAR(5)     NULL,    
                    SubmittedBy              VARCHAR(50)    NULL,    
                    StatusBy                 VARCHAR(50)    NULL,    
                    CompletedBy              VARCHAR(50)    NULL,    
                    PeriodStartDate          DATETIME       NULL,    
                    PeriodEndDate            DATETIME       NULL,    
                    DeliverableComments      VARCHAR(2000)  NULL,    
                    REPORTCAT_CODE           VARCHAR(50)    NULL,    
                    REPORTSTAT_CODE          VARCHAR(50)    NULL,    
                    AgreementId              INT            NULL,    
                    InstitutionId            INT            NULL,    
                    DepartmentId             INT            NULL,    
                    UnitId                   INT            NULL,    
                    SponsorId                INT            NULL,    
                    PrincipalInvestigatorId  INT            NULL,    
                    SubcontractsYN           CHAR(1)        NULL,    
                    SubContractorId          INT            NULL,    
                    SubOutContractorId       INT            NULL,  
				    PAST30DAYS     INT   NULL,      
					PAST60DAYS     INT   NULL,      
					PAST90DAYS     INT   NULL, 
					PAST90PDAYS     INT   NULL,      
					FUT30DAYS     INT   NULL,      
					FUT60DAYS     INT   NULL,      
					FUT90DAYS     INT   NULL,
					FUT90PDAYS     INT   NULL     )    
    
     -- Let Detail Proc do the work.    
     INSERT    @Proposals    
                    (FolderNumber,    
                    InfoEdPropNumber,    
                    Institution,    
                    Department,    
                    Unit,    
                    ChiefCode,    
                    SponsorName,    
                    SponsorTypeName,    
                    SecondarySponsor,    
                    ProposalShortTitle,    
                    PrincipalInvestigator,    
                    ProjectStartDate,    
                    ProjectEndDate,    
                    InstrumentType,    
                    SubContractorName,    
                    BudgetProjectStartDate,    
                    BudgetProjectEndDate,    
                    ProjectAwardedAmount,    
                    BudgetStartDate,    
                    BudgetEndDate,    
                    BudgetFundNumber,    
                    ExpenseAmount,    
                    IndirectExpenses,    
                    DirectExpenses,    
                    CashBalance,    
                    ReportId,    
                    ReportName,    
                    REPORTCAT,    
					REPORTSTATID,  
                    REPORTSTAT,    
                    SubmittedDate,    
                    --DueDate,    
                    StatusDate,    
                    CompletedDate,    
                    DaysFromDueToCompleted,    
                    SubmittedBy,    
                    StatusBy,    
                    CompletedBy,    
                    PeriodStartDate,    
                    PeriodEndDate,    
                    DeliverableComments,    
                    REPORTCAT_CODE,    
                    REPORTSTAT_CODE,    
                    AgreementId,    
                    InstitutionId,    
                    DepartmentId,    
                    UnitId,    
                    SponsorId,    
                    PrincipalInvestigatorId,    
                    SubcontractsYN,    
                    SubContractorId,    
                    SubOutContractorId,      
					 PAST30DAYS,      
					 PAST60DAYS,      
					 PAST90DAYS,   
					 PAST90PDAYS,      
					 FUT30DAYS,      
					 FUT60DAYS,      
					 FUT90DAYS,
					 FUT90PDAYS  )    
     EXECUTE @ReturnCode = dbo.RptFinancialDeliverableMasterDetail  
                              @StartDate               = @StartDate,    
                              @EndDate                 = @EndDate,                                  
							  @DeliverableStatus       = @DeliverableStatus,   -- Get All    
                              @Institution             = @Institution,    
                              @Department              = @Department,    
                              @Unit                    = @Unit,    
                              @ChiefCode               = NULL,    
                              @FundNumber              = @FundNumber,    
                              @PrincipalInvestigator   = @PrincipalInvestigator,     
							  @OriginatingSponsor      = @OriginatingSponsor,    
                              @Sponsor                 = @Sponsor,                                 
                              @SubcontractSpecialist   = @SubcontractSpecialist,    
							  @SelectedHeaderStatus    = NULL,    
                              @DomainUserId            = @DomainUserId    
                                  
     -- check for errors    
     SELECT @Error=@@ERROR    
     IF @Error != 0 OR @ReturnCode != 0    
     BEGIN    
          RETURN -1    
     END    
    
     -- We have to return some search parameter names so they can display in the headers.    
     -- Only if searching on one and only one value    
     IF @Institution IS NOT NULL AND CHARINDEX(',',@Institution) = 0    
          SELECT    TOP 1 @Institution = Institution    
          FROM      @Proposals    
     ELSE    
          SELECT    @Institution = NULL    
         
     IF @Department IS NOT NULL AND CHARINDEX(',',@Department) = 0    
          SELECT    TOP 1 @Department =Department    
          FROM      @Proposals    
     ELSE    
          SELECT    @Department = NULL    
    
     IF @Unit IS NOT NULL AND CHARINDEX(',',@Unit) = 0    
          SELECT    TOP 1 @Unit = Unit    
          FROM      @Proposals    
     ELSE    
          SELECT    @Unit = NULL    
    
     IF @PrincipalInvestigator IS NOT NULL AND CHARINDEX(',',@PrincipalInvestigator) = 0    
          SELECT    TOP 1 @PrincipalInvestigator = PrincipalInvestigator    
          FROM      @Proposals    
     ELSE    
          SELECT    @PrincipalInvestigator = NULL    
         
     IF @OriginatingSponsor IS NOT NULL AND CHARINDEX(',',@OriginatingSponsor) = 0    
          SELECT    TOP 1 @OriginatingSponsor = SecondarySponsor    
          FROM      @Proposals    
     ELSE    
          SELECT    @OriginatingSponsor = NULL    
    
     IF @Sponsor IS NOT NULL AND CHARINDEX(',',@Sponsor) = 0    
          SELECT    TOP 1 @Sponsor = SponsorName    
          FROM      @Proposals    
     ELSE    
          SELECT    @Sponsor = NULL    
    
     -- Return Result Set    
     SELECT    @DeliverableStatus As DeliverableStatus,    
               @Institution AS Institution,    
               @Department AS Department,    
               @Unit AS Unit,    
               @PrincipalInvestigator AS PrincipalInvestigator,    
               @OriginatingSponsor AS OriginatingSponsor,    
               @Sponsor AS Sponsor,    
			   SUM(PAST30DAYS) AS PAST30DAYS,      
			   SUM(PAST60DAYS) AS PAST60DAYS,      
			   SUM(PAST90DAYS) AS PAST90DAYS, 
			   SUM(PAST90PDAYS) AS PAST90PDAYS,      
			   SUM(FUT30DAYS) AS FUT30DAYS,      
			   SUM(FUT60DAYS) AS FUT60DAYS,      
			   SUM(FUT90DAYS) AS FUT90DAYS,
			   SUM(FUT90PDAYS) AS FUT90PDAYS,    
               GroupBy,    
               GroupById1,    
               GroupByName1,    
               GroupById2,    
               GroupByName2    
               --GroupByName3,    
--               SUM(CASE WHEN DueDate between dateadd(mm,-1,@ReportDate) and dateadd(dd, -1,dateadd(mm,0,@ReportDate) )      
--                      
--               END) AS PAST30DAYS,    
--               SUM(CASE WHEN DueDate between dateadd(mm,-2,@ReportDate) and dateadd(dd, -1,dateadd(mm,-1,@ReportDate) )      
--                        
--               END) AS PAST60DAYS,    
--               SUM(CASE WHEN DueDate between dateadd(mm,-3,@ReportDate) and dateadd(dd, -1,dateadd(mm,-2,@ReportDate) )        
--                     
--               END) AS PAST90DAYS,    
--               SUM(CASE WHEN DueDate between dateadd(mm,0,@ReportDate) and dateadd(dd, -1,dateadd(mm,1,@ReportDate)  )     
--                        
--               END) AS FUT30DAYS,    
--               SUM(CASE WHEN DueDate between dateadd(mm,1,@ReportDate) and dateadd(dd, -1,dateadd(mm,2,@ReportDate) )        
--                      
--               END) AS FUT60DAYS,    
--               CASE WHEN DueDate between dateadd(mm,2,@ReportDate) and dateadd(dd, -1,dateadd(mm,3,@ReportDate))                       
--               END AS FUT90DAYS  
  
--     COUNT( CASE WHEN DueDate between dateadd(mm,-1,@ReportDate) and dateadd(dd, -1,dateadd(mm,0,@ReportDate))       
--     THEN   DueDate      
--      ELSE NULL      
--      END )AS PAST30DAYS,         
--     COUNT( CASE WHEN DueDate between dateadd(mm,-2,@ReportDate) and dateadd(dd, -1,dateadd(mm,-1,@ReportDate))       
--     THEN  DueDate      
--      ELSE NULL      
--      END )AS PAST60DAYS ,      
--     COUNT( CASE WHEN DueDate between dateadd(mm,-3,@ReportDate) and dateadd(dd, -1,dateadd(mm,-2,@ReportDate))       
--     THEN  DueDate      
--      ELSE NULL      
--      END )AS PAST90DAYS ,      
--     COUNT( CASE WHEN DueDate between dateadd(mm,0,@ReportDate) and dateadd(dd, -1,dateadd(mm,1,@ReportDate))       
--     THEN  DueDate      
--      ELSE NULL      
--      END )AS FUT30DAYS ,        
--     COUNT( CASE WHEN DueDate between dateadd(mm,1,@ReportDate) and dateadd(dd, -1,dateadd(mm,2,@ReportDate))       
--     THEN DueDate      
--      ELSE NULL      
--      END )AS FUT60DAYS,      
--   COUNT( CASE WHEN DueDate between dateadd(mm,2,@ReportDate) and dateadd(dd, -1,dateadd(mm,3,@ReportDate))       
--     THEN  DueDate      
--      ELSE NULL      
--      END )AS FUT90DAYS   
      
     FROM      (    
               SELECT    @GroupBy AS GroupBy,    
                         CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(InstitutionId,-1)     -- -1 means not set    
                              ELSE NULL    
                         END AS GroupById1,    
                         CASE @GroupBy     
                         WHEN 'ORG' THEN ISNULL(Institution,'UNASSIGNED')    
                              ELSE NULL    
                         END AS GroupByName1,    
                         CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(REPORTSTATID,-1)  -- -1 means not set    
                              ELSE -1    
                         END AS GroupById2,    
                         CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(REPORTSTAT,'UNASSIGNED')    
                              --WHEN 'SC' THEN ISNULL(REPORTSTAT,'UNASSIGNED')    
                              ELSE ISNULL(SubmittedBy,'UNASSIGNED')    
                         END AS GroupByName2,    
					    SUM(PAST30DAYS) AS PAST30DAYS,      
					    SUM(PAST60DAYS) AS PAST60DAYS,      
					    SUM(PAST90DAYS) AS PAST90DAYS,    
					    SUM(PAST90PDAYS) AS PAST90PDAYS,      
					    SUM(FUT30DAYS) AS FUT30DAYS,      
					    SUM(FUT60DAYS) AS FUT60DAYS,      
					    SUM(FUT90DAYS) AS FUT90DAYS,   
					    SUM(FUT90PDAYS) AS FUT90PDAYS     
--                         CASE @GroupBy     
--                              WHEN 'ORG' THEN ISNULL(ChiefCode,'0')    -- -1 means not set    
--                              ELSE '0'    
--                         END AS GroupByName3,    
                         --REPORTSTAT AS DeliverableStatus,    
                         --DueDate--,    
                         --COUNT(ReportId) AS DayCount    
               FROM      @Proposals    
               GROUP BY  CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(InstitutionId,-1)         
                              ELSE NULL    
                         END,    
                         CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(Institution,'UNASSIGNED')    
                              ELSE NULL    
                         END,    
                         CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(REPORTSTATID,-1)  -- -1 means not set    
                              ELSE -1    
                         END,    
                         CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(REPORTSTAT,'UNASSIGNED')    
                              --WHEN 'SC' THEN ISNULL(REPORTSTAT,'UNASSIGNED')    
                              ELSE ISNULL(SubmittedBy,'UNASSIGNED')    
                         END) DC--,    
--                         CASE @GroupBy     
--                              WHEN 'ORG' THEN ISNULL(ChiefCode,'0')    -- -1 means not set    
--                              ELSE '0'    
--                         END,                             
                         --DueDate ) DC    
     GROUP BY  GroupBy,    
               GroupById1,    
               GroupByName1,    
               GroupById2,    
               GroupByName2--,    
               --GroupByName3    
     ORDER BY  GroupBy,    
               GroupByName1,    
               GroupByName2    
                   
     -- check for errors    
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT    
     IF @Error != 0     
     BEGIN    
          RETURN -1    
     END    
         
     -- Return Success    
     RETURN 0  
END  
go

IF OBJECT_ID('dbo.RptFinancialDeliverableMasterReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptCSCRExecutedAgreements >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptFinancialDeliverableMasterReport >>>'
go

GRANT EXECUTE ON dbo.RptFinancialDeliverableMasterReport TO InsightReport,Insight
go
